﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Forms;

namespace SqLiteHelper
{
    public class SQLiteHelper
    { 
        public SQLiteHelper(string conn)
        {
            strConn = conn;
        }
        public SQLiteHelper(string path,string pass)
        {
            strConn = string.Format("Data Source={0};Pooling=true;FailIfMissing=false;Version=3;", path, pass);
            // strConn = string.Format("Data Source={0};Pooling=true;FailIfMissing=false;password={1};Version=3;", path, pass);
            strConn2 = path;
        }
       
        /// <summary>
        /// 创建链接字符串
        private string strConn = string.Empty;
        private string strConn2 = string.Empty;
        #region  执行Sql语句，增删改
        /// <summary>
        /// 执行Sql语句，增删改
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parms">参数</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string sql, params SQLiteParameter[] parms)
        {
          
            using (SQLiteConnection conn = new SQLiteConnection(strConn))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    if (parms != null)
                    {
                        cmd.Parameters.AddRange(parms);
                    }
                   
                    try
                    {
                        conn.Open();
                        return cmd.ExecuteNonQuery();

                    }
                    catch(Exception ex)
                    {
                       
                        return 0;
                    }

                }
            }
        }
        #endregion



        /// <summary>
        /// 新建表
        /// </summary>
        /// <returns></returns>
        public int buildEXcel(string order)
        {
            //构造连接数据库的字符串
            SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder();
            connectionString.DataSource = strConn2;

            //连接数据库
            using (SQLiteConnection conn = new SQLiteConnection(connectionString.ToString()))
            {
                using (var cmd = new SQLiteCommand(conn)) 
                 
                 {
                  
                    if (order != null)
                    {
                        cmd.CommandText = order;
                    }

                    try
                    {
                        conn.Open();
                        return cmd.ExecuteNonQuery();

                    }

                    catch (Exception x)
                    {
                        Console.WriteLine("发生异常：" + x.Message);

                        cmd.Parameters.Clear();
                        connectionString.Clear();
                        return 1;
                    }

                }
              

            } 
          

           // 插入新表头 机柜信息
             


        }
        /// <summary>
        /// addDate
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public int addDate(string sql, params SQLiteParameter[] parms)
        {


            SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder();
            connectionString.DataSource = strConn2;
            //连接数据库
            using (SQLiteConnection conn = new SQLiteConnection(connectionString.ToString()))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    if (parms != null)
                    {
                        cmd.Parameters.AddRange(parms);
                    }

                    try
                    {
                        conn.Open();
                        return cmd.ExecuteNonQuery();

                    }

                    catch (Exception x)
                    {
                        Console.WriteLine("发生异常：" + x.Message);

                        cmd.Parameters.Clear();
                        connectionString.Clear();
                        return 0;
                    }

                  

                }
               

            }
          
        }



        #region 执行Sql语句，1个返回值，返回查询到的数据个数等.
        /// <summary>
        /// 执行一个Sql语句，1个返回值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parms">sql参数</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, params SQLiteParameter[] parms)
        {
            using (SQLiteConnection conn = new SQLiteConnection(strConn))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    if (parms != null)
                    {
                        cmd.Parameters.AddRange(parms);
                    }
                 
                    return cmd.ExecuteScalar();
                }
            }
        }
        #endregion


        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>删除
        #region 执行sql语句，返回结果集

        public SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parms)
        {
            SQLiteConnection conn = new SQLiteConnection(strConn);
            using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
            {
                if (parms != null)
                {
                    cmd.Parameters.AddRange(parms);
                }
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        #endregion

        public DataTable ExecuteReader_V3(string sql, params SQLiteParameter[] parms)
        {
            // 创建 DataSet 对象
            DataTable dataSet = null;
            SQLiteConnectionStringBuilder connectionStringBuilder = new SQLiteConnectionStringBuilder();
            connectionStringBuilder.DataSource = strConn2; // 假设 strConn2 包含正确的数据源路径
            string connectionString = connectionStringBuilder.ToString();
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(connectionString))
                {
                    // 打开连接
                    conn.Open();

                    using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                    {
                        // 如果提供了参数，则添加到命令中
                        if (parms != null)
                        {
                            cmd.Parameters.AddRange(parms);
                        }
                        // 使用 SQLiteDataAdapter 并将命令分配给它的 SelectCommand
                        using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd))
                        {
                            dataSet = new DataTable();
                            // 使用 SQLiteDataAdapter 的 Fill 方法将查询结果填充到 DataSet 中
                            adapter.Fill(dataSet);
                        }
                    }


                }

            }
            catch (Exception)
            {

                throw;
            }
            return dataSet;

        }
    }


    class SQLiteHelperV2
    {
        private string Path = string.Empty;
        public SQLiteHelperV2(string path)
        {

            Path = path;


        }
        /// <summary>
        ///针对于listview控件
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable ExcuteQueryDT(String sql)
        {

            SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder();
            connectionString.DataSource = Path;
            string conStr = connectionString.ToString();

            SQLiteConnection con = null;
            System.Data.DataTable dt = null;
            try
            {
                con = new SQLiteConnection(conStr);
                con.Open();
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, con);
                //SqlCommand com = new SqlCommand();
                //reader = com.ExecuteReader;
                dt = new System.Data.DataTable();
                adapter.Fill(dt);
            }
            catch (System.Exception a)
            {
              
               
            }
            finally
            {
                con.Close();
            }
            return dt;

        }


        /// <summary>
        /// 数据库获取值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ps"></param>
        /// <returns></returns>
        public List<string> getDBValues(string sql, params object[] ps)
        {
            SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder();
            connectionString.DataSource = Path;
            string conStr = connectionString.ToString();
            List<string> values = new List<string>();
            SQLiteConnection conn = new SQLiteConnection(conStr);
         
            try
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddRange(ps);



                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {


                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {

                            //  string value = reader.IsDBNull(i) ? string.Empty : reader.GetValue(i);//这个方法读取到null时会产生一个错误
                            object value = reader.IsDBNull(i) ? string.Empty : reader.GetValue(i);//这个方法读取到null时会产生一个错误
                            //values.Add(value ?? ""); // 将列的值添加到集合中
                          
                            values.Add(value.ToString() ?? "");
                        }

                    }

                  
                }

                cmd.Parameters.Clear();
                cmd.Dispose();
            }
            catch (System.Exception ex)
            {
                // 处理异常
                MessageBox.Show(ex.Message);
                Console.WriteLine("发生异常：" + ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return values;
        }

        public List<List<string>> getDBValues_v2(string sql, params object[] ps)
        {
            SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder();
            connectionString.DataSource = Path;
            string conStr = connectionString.ToString();
            SQLiteConnection conn = new SQLiteConnection(conStr);


            List<List<string>> rowData = new List<List<string>>();//创建二维数据字符串
          

            try
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddRange(ps);



                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {

                    List<string> row = new List<string>();//创建一维数组字符串
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {

                           
                            object value = reader.IsDBNull(i) ? string.Empty : reader.GetValue(i);//这个方法读取到null时会产生一个错误
                            row.Add(value.ToString() ?? "");
                        }
                        rowData.Add(row);
                        row = new List<string>(); // 重置行数据列表，为下一行做准备  

                    }


                }

                cmd.Parameters.Clear();
                cmd.Dispose();
            }
            catch (System.Exception ex)
            {
                // 处理异常
                MessageBox.Show(ex.Message);
                Console.WriteLine("发生异常：" + ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return rowData;
        }

        public Dictionary<int,string > getDBValues_v3(string sql, params object[] ps)
        {
            SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder();
            connectionString.DataSource = Path;
            string conStr = connectionString.ToString();
            SQLiteConnection conn = new SQLiteConnection(conStr);


            Dictionary<int, string> memberMap = new Dictionary<int, string>();


            try
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddRange(ps);



                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        string memberName = reader["name"].ToString();
                        int memberNumber = Convert.ToInt32(reader["id"]);
                        memberMap.Add(memberNumber, memberName);

                    }


                }

                cmd.Parameters.Clear();
                cmd.Dispose();
            }
            catch (System.Exception ex)
            {
                // 处理异常
                MessageBox.Show(ex.Message);
                Console.WriteLine("发生异常：" + ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return memberMap;
        }
    }



}
